/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package diet.dao;

import diet.dto.DietDTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author BadBoy
 */
public class DietDAO {

    public boolean add(DietDTO Diet) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("INSERT INTO [Diet](UserID,DateTimeBegin,DateTimeEnd,DietName,CalorieNeed,isNew) VALUES(?,?,?,?,?,?)");
            stm.setInt(1, Diet.getCustomerID());
            stm.setDate(2, new java.sql.Date(Diet.getDateTimeBegin().getTime()));
            stm.setDate(3, new java.sql.Date(Diet.getDateTimeEnd().getTime()));
            stm.setString(4, Diet.getDietName());
            stm.setInt(5, Diet.getCalorieNeed());
            stm.setInt(6, Diet.getIsNew());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean delete(int dietid) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("DELETE FROM [Diet] WHERE DietID=? ");
            stm.setInt(1, dietid);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean update(DietDTO diet) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("UPDATE [Diet] SET CustomerID=?, DateTimeBegin=?,DateTimeEnd=?,DietName=?,CalorieNeed=?,isNew=? "
                    + " WHERE DietID=?");
            stm.setInt(1, diet.getCustomerID());
            stm.setDate(2, (Date) diet.getDateTimeBegin());
            stm.setDate(3, (Date) diet.getDateTimeEnd());
            stm.setString(4, diet.getDietName());
            stm.setInt(5, diet.getCalorieNeed());
            stm.setInt(6, diet.getIsNew());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    //get newest diet by userID
    //NhuNHU
    public DietDTO getNewestDiet(int userID) {
        Connection con = null;
        PreparedStatement st = null;
        DietDTO diet = new DietDTO();
        try {
            con = DAO.ConnectWithDriver();
            st = con.prepareStatement("SELECT TOP 1 * FROM [Diet] WHERE UserID = ? AND isNew = 1 ORDER BY DateTimeBegin DESC");
            st.setInt(1, userID);

            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                diet.setDietID(rs.getInt("DietID"));
                diet.setDateTimeBegin(rs.getDate("DateTimeBegin"));
                diet.setDateTimeEnd(rs.getDate("DateTimeEnd"));
                diet.setDietName(rs.getString("DietName"));
                diet.setCalorieNeed(rs.getInt("CalorieNeed"));
                diet.setCustomerID(userID);
                diet.setIsNew(rs.getInt("isNew"));
            }
            rs.close();
            con.close();
            return diet;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return null;
        }
    }

    //get newest diet by userID
    //NhuNHU
    public DietDTO getNewestDiet() {
        Connection con = null;
        PreparedStatement st = null;
        DietDTO diet = new DietDTO();
        try {
            con = DAO.ConnectWithDriver();
            st = con.prepareStatement("select * from Diet WHERE   DietID = (SELECT MAX(DietID)  FROM Diet)");


            ResultSet rs = st.executeQuery();

            while (rs.next()) {

                diet.setDietID(rs.getInt("DietID"));
                diet.setDateTimeBegin(rs.getDate("DateTimeBegin"));
                diet.setDateTimeEnd(rs.getDate("DateTimeEnd"));
                diet.setDietName(rs.getString("DietName"));
                diet.setCalorieNeed(rs.getInt("CalorieNeed"));
                diet.setCustomerID(rs.getInt("UserID"));
                diet.setIsNew(rs.getInt("isNew"));
            }
            rs.close();
            con.close();
            return diet;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return null;
        }
    }
}
